const db = require('../db/connMysql');

const harmDao = {
    /*
     * 查询所有学生的咨询记录
     */
    async getAllConsultList() {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql = `select
                               ct.consultationId as consultationId,
                               s.cardId as cardId,
                               s.name as name,
                               f.name as facultyName,
                               c.name as className,
                               ct.consultationTopic as consultationTopic,
                               u.username as counselor,
                               ct.isWarning as isWarning,
                               DATE_FORMAT(ct.startTime, '%Y-%m-%d') as consultTime
                           from Consultations as ct
                           left join Students as s on ct.studentId = s.studentId
                           left join Classes as c on s.classId = c.classId
                           left join Facultys as f on c.facultyId = f.facultyId
                           left join Counselors as cs on ct.counselorId = cs.counselorId
                           left join Users as u on cs.uid = u.uid`;
                connection.query(sql, [], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return result;
    },
    /*
     * 查询某个咨询师的所有咨询记录
     */
    async getAllConsultListOfCounselor(uid) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql = `select
                               ct.consultationId as consultationId,
                               s.cardId as cardId,
                               s.name as name,
                               f.name as facultyName,
                               c.name as className,
                               ct.consultationTopic as consultationTopic,
                               u.username as counselor,
                               ct.isWarning as isWarning,
                               DATE_FORMAT(ct.startTime, '%Y-%m-%d') as consultTime
                           from Consultations as ct
                           left join Students as s on ct.studentId = s.studentId
                           left join Classes as c on s.classId = c.classId
                           left join Facultys as f on c.facultyId = f.facultyId
                           left join Counselors as cs on ct.counselorId = cs.counselorId
                           left join Users as u on cs.uid = u.uid
                           where cs.uid = ?`;
                connection.query(sql, [uid], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return result;
    },
    /*
     * 查询某个学院的所有咨询记录
     */
    async getAllConsultListOfCollegeMaster(uid) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql = `select
                               ct.consultationId as consultationId,
                               s.cardId as cardId,
                               s.name as name,
                               f.name as facultyName,
                               c.name as className,
                               ct.consultationTopic as consultationTopic,
                               u.username as counselor,
                               ct.isWarning as isWarning,
                               DATE_FORMAT(ct.startTime, '%Y-%m-%d') as consultTime
                           from Consultations as ct
                           left join Students as s on ct.studentId = s.studentId
                           left join Classes as c on s.classId = c.classId
                           left join Facultys as f on c.facultyId = f.facultyId
                           left join Counselors as cs on ct.counselorId = cs.counselorId
                           left join Users as u on cs.uid = u.uid
                           where f.facultyId = (select facultyId from Users where uid = ?)`;
                connection.query(sql, [uid], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return result;
    },
    /*
     * 查询某个学生的所有咨询记录
     */
    async getConsultRecordByStudent(studentId, uid) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                // 需要 咨询时间、咨询主题、咨询问题、咨询师
                let sql = `select
                               cs.consultationId as consultId,
                               DATE_FORMAT(cs.startTime, '%Y-%m-%d') as consultTime,
                               it.name as issueType,
                               cs.consultationTopic as consultationTopic,
                               u.username as counselor
                           from Consultations as cs
                           left join IssueTypes as it on cs.issueTypeId = it.issueTypeId
                           left join Counselors on cs.counselorId = Counselors.counselorId
                           left join Users as u on Counselors.uid = u.uid
                           where cs.studentId = ?`;
                connection.query(sql, [studentId], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return result;
    },
    /*
     * 查询某个咨询师以及对应学生的所有咨询记录
     */
    async getConsultRecordByCounselor(studentId, uid) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                // 需要 咨询时间、咨询主题、咨询问题、咨询师
                let sql = `select
                               cs.consultationId as consultId,
                               DATE_FORMAT(cs.startTime, '%Y-%m-%d') as consultTime,
                               it.name as issueType,
                               cs.consultationTopic as consultationTopic,
                               u.username as counselor
                           from Consultations as cs
                           left join IssueTypes as it on cs.issueTypeId = it.issueTypeId
                           left join Counselors on cs.counselorId = Counselors.counselorId
                           left join Users as u on Counselors.uid = u.uid
                           left join Students as s on cs.studentId = s.studentId
                           left join Classes as c on s.classId = c.classId
                           where cs.studentId = ? and cs.counselorId = (select counselorId from Counselors where uid = ? LIMIT 1)`;
                connection.query(sql, [studentId, uid], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return result;
    },
    /*
     * 查询某个学生的所有咨询记录限制学院
     */
    async getConsultRecordByStudentOfFaculty(studentId, uid) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                // 需要 咨询时间、咨询主题、咨询问题、咨询师
                let sql = `select
                               cs.consultationId as consultId,
                               DATE_FORMAT(cs.startTime, '%Y-%m-%d') as consultTime,
                               it.name as issueType,
                               cs.consultationTopic as consultationTopic,
                               u.username as counselor
                           from Consultations as cs
                           left join IssueTypes as it on cs.issueTypeId = it.issueTypeId
                           left join Counselors on cs.counselorId = Counselors.counselorId
                           left join Users as u on Counselors.uid = u.uid
                           left join Students as s on cs.studentId = s.studentId
                           left join Classes as c on s.classId = c.classId
                           where cs.studentId = ? and c.facultyId = (select facultyId from Users where uid = ? LIMIT 1)`;
                connection.query(sql, [studentId, uid], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return result;
    },
    /*
     * 新增咨询师自己的咨询记录
     * addConsultselfRecord
     */
    async addConsultSelfRecord(data) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let insertData = [
                    data.studentId, data.startTime, data.endTime, data.issueTypeId,
                    data.consultationTopic, data.counselorId, data.isWarning,
                    data.subjectiveDescription, data.objectiveDescription, data.counselorAssessment,
                    data.counselorNotes, data.consultationMethodId, data.handlingMethod, data.isClose
                ];
                let sql = `insert into Consultations (
                            studentId, startTime, endTime, issueTypeId,
                            consultationTopic, counselorId, isWarning,
                            subjectiveDescription, objectiveDescription, counselorAssessment,
                            counselorNotes, consultationMethodId, handlingMethod, isClose
                        ) values (?, ?, ?, ?, ?, (select counselorId from Counselors where uid = ? limit 1), ?, ?, ?, ?, ?, ?, ?, ?)`;
                connection.query(sql, insertData, function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return result;
    },
    /*
     * 新增咨询记录
     */
    async addConsultRecord(data) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let insertData = [
                    data.studentId, data.startTime, data.endTime, data.issueTypeId,
                    data.consultationTopic, data.counselorId, data.isWarning,
                    data.subjectiveDescription, data.objectiveDescription, data.counselorAssessment,
                    data.counselorNotes, data.consultationMethodId, data.handlingMethod, data.isClose
                ];
                let sql = `insert into Consultations (
                            studentId, startTime, endTime, issueTypeId,
                            consultationTopic, counselorId, isWarning,
                            subjectiveDescription, objectiveDescription, counselorAssessment,
                            counselorNotes, consultationMethodId, handlingMethod, isClose
                        ) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;
                connection.query(sql, insertData, function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return result;
    },
    /*
     * 查询咨询记录详情
     */
    async getConsultRecordInfo(consultationId) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql = `select
                               ct.consultationId as consultationId,
                               ct.studentId as studentId,
                               s.cardId as cardId,
                               s.name as name,
                               c.name as className,
                               f.name as facultyName,
                               g.name as grade,
                               DATE_FORMAT(ct.startTime, '%Y-%m-%d') as startTime,
                               DATE_FORMAT(ct.endTime, '%Y-%m-%d') as endTime,
                               it.issueTypeId as issueTypeId,
                               it.name as issueType,
                               ct.consultationTopic as consultationTopic,
                               u.username as counselor,
                               ct.counselorId as counselorId,
                               ct.isWarning as isWarning,
                               ct.subjectiveDescription as subjectiveDescription,
                               ct.objectiveDescription as objectiveDescription,
                               ct.counselorAssessment as counselorAssessment,
                               ct.counselorNotes as counselorNotes,
                               cm.method as consultationMethod,
                               ct.consultationMethodId as consultationMethodId,
                               ct.handlingMethod as handlingMethod,
                               ct.isClose as isClose
                           from Consultations as ct
                           left join Students as s on ct.studentId = s.studentId
                           left join Classes as c on s.classId = c.classId
                           left join Facultys as f on c.facultyId = f.facultyId
                           left join Grades as g on c.gradeId = g.gradeId
                           left join IssueTypes as it on ct.issueTypeId = it.issueTypeId
                           left join Counselors as cs on ct.counselorId = cs.counselorId
                           left join Users as u on cs.uid = u.uid
                           left join ConsultationMethods as cm on ct.consultationMethodId = cm.consultationMethodId
                           where ct.consultationId = ?`;
                connection.query(sql, [consultationId], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return result;
    },
    /*
     * 查询咨询师自己的某一条咨询记录详情
     */
    async getConsultRecordInfoOfCounselor(consultationId, uid) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql = `select
                               ct.consultationId as consultationId,
                               ct.studentId as studentId,
                               s.cardId as cardId,
                               s.name as name,
                               c.name as className,
                               f.name as facultyName,
                               g.name as grade,
                               DATE_FORMAT(ct.startTime, '%Y-%m-%d') as startTime,
                               DATE_FORMAT(ct.endTime, '%Y-%m-%d') as endTime,
                               it.issueTypeId as issueTypeId,
                               it.name as issueType,
                               ct.consultationTopic as consultationTopic,
                               u.username as counselor,
                               ct.counselorId as counselorId,
                               ct.isWarning as isWarning,
                               ct.subjectiveDescription as subjectiveDescription,
                               ct.objectiveDescription as objectiveDescription,
                               ct.counselorAssessment as counselorAssessment,
                               ct.counselorNotes as counselorNotes,
                               cm.method as consultationMethod,
                               ct.consultationMethodId as consultationMethodId,
                               ct.handlingMethod as handlingMethod,
                               ct.isClose as isClose
                           from Consultations as ct
                           left join Students as s on ct.studentId = s.studentId
                           left join Classes as c on s.classId = c.classId
                           left join Facultys as f on c.facultyId = f.facultyId
                           left join Grades as g on c.gradeId = g.gradeId
                           left join IssueTypes as it on ct.issueTypeId = it.issueTypeId
                           left join Counselors as cs on ct.counselorId = cs.counselorId
                           left join Users as u on cs.uid = u.uid
                           left join ConsultationMethods as cm on ct.consultationMethodId = cm.consultationMethodId
                           where ct.consultationId = ? and u.uid = ?`;
                connection.query(sql, [consultationId, uid], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return result;
    },
    /*
     * 查询操作用户所属学院内的某一条咨询记录详情
     */
    async getConsultRecordInfoOfCollegeMaster(consultationId, uid) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql = `select
                               ct.consultationId as consultationId,
                               ct.studentId as studentId,
                               s.cardId as cardId,
                               s.name as name,
                               c.name as className,
                               f.name as facultyName,
                               g.name as grade,
                               DATE_FORMAT(ct.startTime, '%Y-%m-%d') as startTime,
                               DATE_FORMAT(ct.endTime, '%Y-%m-%d') as endTime,
                               it.issueTypeId as issueTypeId,
                               it.name as issueType,
                               ct.consultationTopic as consultationTopic,
                               u.username as counselor,
                               ct.counselorId as counselorId,
                               ct.isWarning as isWarning,
                               ct.subjectiveDescription as subjectiveDescription,
                               ct.objectiveDescription as objectiveDescription,
                               ct.counselorAssessment as counselorAssessment,
                               ct.counselorNotes as counselorNotes,
                               cm.method as consultationMethod,
                               ct.consultationMethodId as consultationMethodId,
                               ct.handlingMethod as handlingMethod,
                               ct.isClose as isClose
                           from Consultations as ct
                           left join Students as s on ct.studentId = s.studentId
                           left join Classes as c on s.classId = c.classId
                           left join Facultys as f on c.facultyId = f.facultyId
                           left join Grades as g on c.gradeId = g.gradeId
                           left join IssueTypes as it on ct.issueTypeId = it.issueTypeId
                           left join Counselors as cs on ct.counselorId = cs.counselorId
                           left join Users as u on cs.uid = u.uid
                           left join ConsultationMethods as cm on ct.consultationMethodId = cm.consultationMethodId
                           where ct.consultationId = ? and f.facultyId = (select facultyId from Users where uid = ? LIMIT 1)`;
                connection.query(sql, [consultationId, uid], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return result;
    },
    /*
     * 修改咨询记录信息
     */
    async updateConsultRecordInfo(data) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let updateData = [
                    data.studentId, data.startTime, data.endTime, data.issueTypeId,
                    data.consultationTopic, data.counselorId, data.isWarning,
                    data.subjectiveDescription, data.objectiveDescription, data.counselorAssessment,
                    data.counselorNotes, data.consultationMethodId, data.handlingMethod, data.isClose,
                    data.consultationId
                ];
                let sql = `update
                               Consultations
                           set
                               studentId = ?, startTime = ?, endTime = ?, issueTypeId = ?,
                               consultationTopic = ?, counselorId = ?, isWarning = ?,
                               subjectiveDescription = ?, objectiveDescription = ?, counselorAssessment = ?,
                               counselorNotes = ?, consultationMethodId = ?, handlingMethod = ?, isClose = ?
                           where consultationId = ?`;
                connection.query(sql, updateData, function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return result;
    },
    /*
     * 删除咨询记录接口
     */
    async deleteConsultRecord(consultationId) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql = `delete from Consultations where consultationId = ?`;
                connection.query(sql, [consultationId], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return result;
    },
    /*
     * 获取所有咨询师列表
     */
    async getAllCounselorList() {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql = `select
                               u.uid as uid, u.username as name, cs.counselorId as id
                           from Users as u
                           left join Counselors as cs on u.uid = cs.uid
                           where u.userTypeId = (select userTypeId from UserType where name = '咨询师' LIMIT 1)`;
                connection.query(sql, [], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return result;
    },
    /*
     * 查看所有咨询方式
     */
    async getAllConsultationMethods() {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql = `select * from ConsultationMethods`;
                connection.query(sql, [], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return result;
    },
    /*
     * 查看所有问题类型
     */
    async getAllIssueTypes() {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql = `select * from IssueTypes`;
                connection.query(sql, [], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return result;
    },
    /*
     * 查看所有咨询主题
     */
    // async getAllConsultationTopics() {
    //     let result = await new Promise((resolve, reject) => {
    //         db.getConnection((err, connection) => {
    //             if (err) {
    //                 // 连不上数据库
    //                 reject(err);
    //                 return ;
    //             }
    //             let sql = `select * from ConsultationTopics`;
    //             connection.query(sql, [], function (err, result) {
    //                 if (err) {
    //                     // 连上数据库之后，执行sql时发生错误
    //                     reject(err);
    //                     return ;
    //                 }
    //                 connection.release();
    //                 resolve(result);
    //             });
    //         });
    //     });
    //     return result;
    // }
};

module.exports = harmDao;
